package cn.doitedu.demo;

import java.sql.*;

/***
 * @author hunter.d
 * @qq 657270652
 * @wx haitao-duan
 * @date 2021/3/16
 **/
public class ClickhouseJdbc {
    public static void main(String[] args) throws Exception {
        String ckDriver = "com.github.housepower.jdbc.ClickHouseDriver";
        String ckUrl = "jdbc:clickhouse://192.168.77.63:9000/default";
        String table = "event_detail";

        Class.forName(ckDriver);
        Connection conn = DriverManager.getConnection(ckUrl);
        String sql = "SELECT\n" +
                "    deviceId,\n" +
                "    sequenceMatch('.*(?1).*(?2).*(?3)')(\n" +
                "    toDateTime(`timeStamp`),\n" +
                "    eventId = 'share' and properties['pageId']='6',\n" +
                "    eventId = 'addCart' and properties['pageId']='5',\n" +
                "    eventId = 'collect' and properties['productId']='8'\n" +
                "  ) as is_match3,\n" +
                "    sequenceMatch('.*(?1).*(?2)')(\n" +
                "    toDateTime(`timeStamp`),\n" +
                "    eventId = 'share' and properties['pageId']='6',\n" +
                "    eventId = 'addCart' and properties['pageId']='5'\n" +
                "  ) as is_match2,\n" +
                "    sequenceMatch('.*(?1).*')(\n" +
                "    toDateTime(`timeStamp`),\n" +
                "    eventId = 'share' and properties['pageId']='6',\n" +
                "    eventId = 'addCart' and properties['pageId']='5'\n" +
                "  ) as is_match1\n" +
                "from event_detail\n" +
                "where deviceId='015' and  `timeStamp` >= 1616143200000 and `timeStamp` <= 1616145644106\n" +
                "  and (\n" +
                "        (eventId='share' and properties['pageId']='6')\n" +
                "        or\n" +
                "        (eventId = 'addCart' and properties['pageId']='5')\n" +
                "        or\n" +
                "        (eventId = 'collect' and properties['productId']='8')\n" +
                "    )\n" +
                "group by deviceId\n" +
                ";\n";
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1,"nJBTQejDxDmc");
        long s = System.currentTimeMillis();
        ResultSet resultSet = st.executeQuery();
        ResultSetMetaData rsmd = resultSet.getMetaData();
        int maxStep = 0;
        int stepAmt = 3;
        while (resultSet.next()) {
            for (int i = 2; i <= stepAmt + 1; i++) {
                int step = resultSet.getInt(i);
                if (step == 1) {
                    maxStep = stepAmt - i + 2;
                    break;
                }
            }
        }


        long e = System.currentTimeMillis();
        System.out.println(maxStep);
        System.out.println(e-s);
        conn.close();
    }
}
